<?php
/**
 * Created by PhpStorm.
 * User: root
 * Date: 17-3-16
 * Time: 上午9:37
 */
namespace Inbound\Service;
use Inbound\Service\PublicInfoService;
use \Api\Controller\CrontabController;

class ReportService {
    //期初库存
    public $summaryInventory       = null;
    //库存明细
    public $currentInventory       = null;
    //查询记录总数
    public $count = 0;
    //页面展示包含页面样式
    public $page = 0;
    //数据
    public $data = array();

    public $nowTime = null;

    public function __construct($table = '',$param = array()) {
//        $this->summaryInventory            = D('Inbound/Report','Model');
        $this->currentInventory            = M('report_current_inventory', 'api_', 'fbawarehouse');
        $this->summaryInventory            = M('report_summary_inventory', 'api_', 'fbawarehouse');
        $this->reportCheck                 = M('report_sc_check', 'api_', 'fbawarehouse');
        $this->reportLog                 = M('report_log', 'api_', 'fbawarehouse');
        $this->nowTime                  = date('Y-m-d H:i:s',time());
    }
    /**
     * 描述: 数据查询
     * 作者: kelvin
     */
    public function select($arr = array()) {
        set_time_limit(0);
        ini_set('memory_limit', '2048M');
        $accounts = M('amazonorder_accounts',' ','fbawarehouse')
            ->getField('id,name');
        $where = array();
        if($arr['account_id']){
            $where['account_id'] = $arr['account_id'];
        }
        if($arr['sku']){
            $where['sku'] = htmlspecialchars_decode($arr['sku']);
        }
        if($arr['date']){
            $start = date('Y-m', strtotime(date($arr['date'].'-01', strtotime('-1 month')) . '-1 day'));
            $end = $arr['date'];
            $where['LEFT(snapshot_date,7)'] = array(
                "in","$start,$end"
            );
        }else{
            $start = date('Y-m', strtotime(date('Y-m-01', strtotime('-1 month')) . '-1 day'));
            $end = date('Y-m', strtotime(date('Y-m-01') . '-1 day'));
        }
        $result = $this->currentInventory
            ->where($where)
            ->group('account_id,sku')
            ->field("account_id,sku,SUM(if(LEFT(snapshot_date,7)='$start',quantity,0)) AS 'qckc',
                SUM(if(LEFT(snapshot_date,7)='$end',quantity,0)) AS 'qmkc'")
            ->select();
        if($arr['down']){
            $data = $result;
        }else{
            $this->count = count($result);
            $Page = new \Org\Util\Page($this->count,20);// 实例化分页类 传入总记录数和每页显示的记录数(5)
            $this->page = $Page->show();// 分页显示输出
            $data = $this->currentInventory
                ->where($where)
                ->group('account_id,sku')
                ->field("account_id,sku,SUM(if(LEFT(snapshot_date,7)='$start',quantity,0)) AS 'qckc',
                SUM(if(LEFT(snapshot_date,7)='$end',quantity,0)) AS 'qmkc'")
                ->limit($Page->firstRow.','.$Page->listRows)
                ->order('account_id desc')
                ->select();
        }
        $lists = $this->listQuantity($arr['date']);
        $sku = $this->getSku();
        foreach($data as $k =>&$v) {
            $list = $lists[$v['account_id'].$v['sku']];
            $v['account_name'] = $accounts[$v['account_id']]?$accounts[$v['account_id']]:NULL;
            $v['piv_sku'] = $sku[$v['account_id'].trim($v['sku'])]['private_sku'];
            $v['bhrk'] = $list['bhrk']?$list['bhrk']:0;
            $v['thrk'] = $list['thrk']?$list['thrk']:0;
            $v['pyrk'] = $list['pyrk']?$list['pyrk']:0;
            $v['pkck'] = $list['pkck']?$list['pkck']:0;
            $v['zcrk'] = $list['zcrk']?$list['zcrk']:0;
            $v['zcck'] = $list['zcck']?$list['zcck']:0;
            $v['xsck'] = $list['xsck']?$list['xsck']:0;
            $v['qtck'] = $list['qtck']?$list['qtck']:0;
            $v['rkhj'] = $v['bhrk']+$v['thrk']+$v['pyrk']+$v['zcrk'];
            $v['ckhj'] = $v['xsck']+$v['pkck']+$v['qtck']+$v['zcck'];
            $v['llqmkc'] = $v['qckc']+$v['rkhj']-$v['ckhj'];
            $v['diff'] = $v['qmkc']-$v['llqmkc'];
        }
        return $data;
    }
    /**
     * 描述: 库存明细
     * 作者: kelvin
     */
    public function listQuantity($date) {
        $where = array(
//            'account_id' => $account_id,
            'LEFT(snapshot_date,7)' =>$date
        );
        $result = $this->summaryInventory
            ->where($where)
            ->group('account_id,sku')
            ->getField("concat(`account_id`,`sku`) as account_sku,`account_id`,
                `sku`,
                sum(if(`transaction_type`='Receipts',`quantity`,0)) as 'bhrk',
                sum(if(`transaction_type`='CustomerReturns',`quantity`,0)) as 'thrk',
                sum(if(`transaction_type`='Adjustments' AND `quantity`>0,quantity,0)) as 'pyrk',
                abs(sum(if(`transaction_type`='Adjustments' AND `quantity`<0,quantity,0))) as 'pkck',
                abs(sum(if(`transaction_type`='WhseTransfers' AND `quantity`>0,quantity,0))) as 'zcrk',
                abs(sum(if(`transaction_type`='WhseTransfers' AND `quantity`<0,quantity,0))) as 'zcck',
                abs(sum(if(`transaction_type`='Shipments',`quantity`,0))) as 'xsck',
                abs(sum(if(`transaction_type`='VendorReturns',`quantity`,0))) as 'qtck'");
        return $result?$result:array();

    }
    /**
     * 描述: 拉取报
     * 作者: kelvin
     */
    public function apiGet($account_id = null) {
        set_time_limit(0);
        if($account_id){
            $accounts[]['id'] = $account_id;
        }else{
            $accounts = PublicInfoService::get_accounts();
        }
        $report = D('Api/Amazon/Report','Service');
        foreach($accounts as $v){
            $accountId = $v['id'];
            $site = PublicInfoService::get_siteid_by_accountid($accountId);
            for($i=0;$i<3;$i++){
                //期初库存
                if($i==0){
                    $map['account_id'] = $accountId;
                    $map['LEFT(snapshot_date,7)'] = date('Y-m', strtotime(date('Y-m-01', strtotime('-1 month')) . '-1 day'));
                    if(date('d')==31){
                        $map['LEFT(snapshot_date,7)'] = date('Y-m', strtotime(date('Y-m-01', strtotime('-1 month -1 day')) . '-1 day'));
                    }
                    $data = M('api_report_current_inventory', ' ', 'fbawarehouse')
                        ->where($map)
                        ->find();
                    if (!empty($data)) {
                        continue;
                    }
                    $options['report_type'] = 6;
                    //日本站点
                    if($site==55){
                        $options['StartDate'] = date('Y-m-d H:i:s', strtotime(date('Y-m-01 00:00:00', strtotime('-1 month')) . '-1 day')-3600*9);
                        $options['EndDate'] = date('Y-m-d H:i:s', strtotime(date('Y-m-01 23:59:59', strtotime('-1 month')) . '-1 day')-3600*9);
                    }
                    else{
                        $options['StartDate'] = date('Y-m-d H:i:s', strtotime(date('Y-m-01 00:00:00', strtotime('-1 month')) . '-1 day')+3600*8);
                        $options['EndDate'] = date('Y-m-d H:i:s', strtotime(date('Y-m-01 23:59:59', strtotime('-1 month')) . '-1 day')+3600*8);
                    }
                    $report->getReport($options,$accountId);
                }
                //期末库存
                if($i==1){
                    $map['account_id'] = $accountId;
                    $map['LEFT(snapshot_date,7)'] = date('Y-m', strtotime('-1 month'));
                    if(date('d')==31){
                        $map['LEFT(snapshot_date,7)'] = date('Y-m', strtotime('-1 month -1 day'));
                    }
                    $data = M('api_report_current_inventory', ' ', 'fbawarehouse')
                        ->where($map)
                        ->find();
                    if (!empty($data)) {
                        continue;
                    }
                    $options['report_type'] = 6;
                    if($site==55){
                        $options['StartDate'] = date('Y-m-d H:i:s',strtotime(date('Y-m-d 00:00:00', strtotime(date('Y-m-01') . '-1 day')))-3600*9);
                        $options['EndDate'] = date('Y-m-d H:i:s', strtotime(date('Y-m-d 23:59:59', strtotime(date('Y-m-01') . '-1 day')))-3600*9);
                    }else{
                        $options['StartDate'] = date('Y-m-d H:i:s',strtotime(date('Y-m-d 00:00:00', strtotime(date('Y-m-01') . '-1 day')))+3600*8);
                        $options['EndDate'] = date('Y-m-d H:i:s', strtotime(date('Y-m-d 23:59:59', strtotime(date('Y-m-01') . '-1 day')))+3600*8);
                    }
                    $report->getReport($options,$accountId);
                }
                //出入库明细
                if($i==2){
                    $map['account_id'] = $accountId;
                    $map['LEFT(snapshot_date,7)'] = date('Y-m', strtotime('-1 month'));
                    if(date('d')==31){
                        $map['LEFT(snapshot_date,7)'] = date('Y-m', strtotime('-1 month -1 day'));
                    }
                    $data = M('api_report_summary_inventory', ' ', 'fbawarehouse')
                        ->where($map)
                        ->find();
                    if (!empty($data)) {
                        continue;
                    }
                    $options['report_type'] = 7;
                    if($site==55) {
                        $options['StartDate'] = date('Y-m-d',strtotime(date('Y-m-01 H:i:s', strtotime(date('Y-m-01') . '-1 day')))-3600*9);
                        $options['EndDate'] = date('Y-m-d',strtotime(date('Y-m-d 23:59:59', strtotime(date('Y-m-01') . '-1 day')))-3600*9);
                    }else{
                        $options['StartDate'] = date('Y-m-d',strtotime(date('Y-m-01 H:i:s', strtotime(date('Y-m-01') . '-1 day')))+3600*8);
                        $options['EndDate'] = date('Y-m-d',strtotime(date('Y-m-d 23:59:59', strtotime(date('Y-m-01') . '-1 day')))+3600*8);
                    }
                    $report->getReport($options,$accountId);
                }
            }
        }
    }
    /**
     * 描述: 审核
     * 作者: kelvin
     */
    public function check($data) {
        $check = array(
            'account_id' => $data['account_id'],
            'cycle_date' => $data['date'],
            'site_id' => PublicInfoService::get_siteid_by_accountid($data['account_id']),
            'check_man' => $_SESSION['current_account']['id'],
            'check_time' => date('Y-m-d H:i:s', time()),
            'pass' => $data['pass'],
            'remark' => $data['remark']?$data['remark']:''
        );
        if($this->reportCheck->add($check)){
            echo "<script>alert('操作成功');window.location.href = 'index?account_id=".$data['account_id']."&date=".$data['date']."'</script>";
        }else{
            echo "<script>alert('操作失败');history.go(-1);</script>";
        }
    }
    /**
     * 描述: 获取审核
     * 作者: kelvin
     */
    public function checkAgree($data) {
        $where = array(
            'account_id' => $data['account_id'],
            'cycle_date' => $data['date'],
        );
        $result = $this->reportCheck->where($where)->field('pass,remark')->find();
        return $result?$result:array();
    }
    /**
     * 描述: 获取sku
     * 作者: kelvin
     */
    public function getSku() {
       $result =  M('api_account_seller_sku', ' ', 'fbawarehouse')
            ->where(1)
           ->group('account_id,seller_sku')
            ->getField('concat(account_id,seller_sku) as account_sku,private_sku');
       return $result?$result:array();
    }
    /**
     * 描述: 已审核列表
     * 作者: kelvin
     */
    public function agreeDown($array = array()) {
        $where = array('cycle_date'=>$array['date']);
        $result = $this->reportCheck
            ->where($where)
            ->group('cycle_date,account_id')
            ->getField('account_id,site_id,check_man,check_time,pass,remark');
        $accounts = PublicInfoService::get_accounts();
        $site = PublicInfoService::get_site_array();
        foreach($accounts as $k => &$v) {
            $v['site_id'] = $site[PublicInfoService::get_siteid_by_accountid($v['id'])];
            $v['cycle_date'] = $array['date'];
            if(in_array($v['id'],array_keys($result))){
                $v['check_man'] = PublicInfoService::get_user_name_by_id($result[$v['id']]['check_man']);
                $v['check_time'] = $result[$v['id']]['check_time'];
                $v['pass'] = $result[$v['id']]['pass']==10?'审核通过':'审核不通过';
                $v['remark'] = $result[$v['id']]['remark'];
            }else{
                $v['check_man'] = '';
                $v['check_time'] = '';
                $v['pass'] = '未审核';
                $v['remark'] = '';
            }
            unset($v['id']);
        }
        $output = fopen('php://output', 'w') or die("can't open php://output");
        //告诉浏览器这个是一个csv文件
        $filename = "FBA库存审核列表".date('Ymd');
        header("Content-Type: application/csv");
        header("Content-Disposition: attachment; filename=$filename.csv");
        ob_end_clean();//关闭缓存
        //输出表头
        $table_head = array(
            '店铺帐号名',
            '站点',
            '数据月份',
            '审核人',
            '审核时间',
            '审核状态',
            '备注'
        );
        fputcsv($output, $table_head);
        foreach($accounts as $val){//$data_arr是数据集，二维数组
            fputcsv($output, array_values($val));
        }
        //关闭文件句柄
        fclose($output) or die("can't close php://output");
        exit;
    }
    /**
     * 描述: 账号拉取列表
     * 作者: kelvin
     */
    public function unDown($array = array()) {
        $where5 = array('date'=>$array['date'],'report_type'=>'5');
        $result5 = $this->reportLog
            ->where($where5)
            ->group('date,account_id,report_type')
            ->getField('account_id,date,remark,is_ok');
        $where6 = array('date'=>$array['date'],'report_type'=>'6');
        $result6 = $this->reportLog
            ->where($where6)
            ->group('date,account_id,report_type')
            ->getField('account_id,date,remark,is_ok');
        $where7 = array('date'=>$array['date'],'report_type'=>'7');
        $result7 = $this->reportLog
            ->where($where7)
            ->group('date,account_id,report_type')
            ->getField('account_id,date,remark,is_ok');
        $accounts = PublicInfoService::get_accounts();
//        $site = PublicInfoService::get_site_array();
        $start = $accounts;
        $end = $accounts;
        $detail = $accounts;
        foreach($start as $k => &$v) {
//            $v['site_id'] = $site[PublicInfoService::get_siteid_by_accountid($v['id'])];
            $v['date'] = $array['date'];
            $v['report_name'] = '期初库存';
            if(in_array($v['id'],array_keys($result5))){
                $v['remark'] = $result5[$v['id']]['is_ok']==1?'插入数据成功':'插入数据失败';
            }else{
                $v['remark'] = '拉取接口失败';
            }
            unset($v['id']);
        }
        foreach($end as $k => &$v) {
            //            $v['site_id'] = $site[PublicInfoService::get_siteid_by_accountid($v['id'])];
            $v['date'] = $array['date'];
            $v['report_name'] = '期末库存';
            if(in_array($v['id'],array_keys($result6))){
                $v['remark'] = $result6[$v['id']]['is_ok']==1?'插入数据成功':'插入数据失败';
            }else{
                $v['remark'] = '拉取接口失败';
            }
            unset($v['id']);
        }
        foreach($detail as $k => &$v) {
            //            $v['site_id'] = $site[PublicInfoService::get_siteid_by_accountid($v['id'])];
            $v['date'] = $array['date'];
            $v['report_name'] = '出入库明细';
            if(in_array($v['id'],array_keys($result7))){
                $v['remark'] = $result7[$v['id']]['is_ok']==1?'插入数据成功':'插入数据失败';
            }else{
                $v['remark'] = '拉取接口失败';
            }
            unset($v['id']);
        }
        $all = array_merge($start,$end,$detail);
        $output = fopen('php://output', 'w') or die("can't open php://output");
        //告诉浏览器这个是一个csv文件
        $filename = "FBA进销存存账号拉取列表列表".date('Ymd');
        header("Content-Type: application/csv");
        header("Content-Disposition: attachment; filename=$filename.csv");
        ob_end_clean();//关闭缓存
        //输出表头
        $table_head = array(
            '店铺帐号名',
            '数据月份',
            '数据类型',
            '是否抓取成功'
        );
        fputcsv($output, $table_head);
        foreach($all as $val){//$data_arr是数据集，二维数组
            fputcsv($output, array_values($val));
        }
        //关闭文件句柄
        fclose($output) or die("can't close php://output");
        exit;
    }
}